#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/8/30 11:53
# @Author : 闫峰
# @File    : sku_filter.py
import datetime

import requests

from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
import os
import threading
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class SkuFilter:
    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

    source_id = 14267
    product_rules_template_id = None
    listing_filter = {
        "first_time": None,#['2023-04-01 00:00:00','2023-05-1 23:59:59'],
        "good_num": [0,10],
        "views": None,
        "sale": [1, 100],
        "sku_sales_platform": 'all',
        "sku_sales_days": None,
        "sku_sales": [3,10],
        "publish_count": 3,
        "first_publist_time": None
    }

    # sku_list = {'10001240'}

    def sku_filter(self, product_rules_template_id):
# TODO
# 下架：`status` in (1,3)，  删除：`status` in (1,2,3);  售价过滤
        sql1 = """
            SELECT tsl.id,tsl.first_publish_time
            FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing tsl
            WHERE tsl.source_id=%s 
            AND tsl.`status` in (1,3)
                """
        res1 = self.db.fetch_all_tuple(sql1, (product_rules_template_id,))
        res1 = set(map(lambda x: x[0], res1))
        # res1 = res1 & self.sku_list
        # listing表 过滤
        data = list(filter(self.filter_listing, res1))
        # 执行队列 过滤
        data = list(filter(self.filter_queue_detail, data))
        # 产品上架到店铺的时间过滤
        if self.listing_filter.get("first_publist_time"):
            data = list(filter(self.filter_listing_by_first_publish_time, data))
        # 可用库存 过滤
        if self.listing_filter.get("good_num"):
            data = list(filter(self.dynamic_inventory_filter, data))
        # 浏览量 过滤
        if self.listing_filter.get("views"):
            data = list(filter(self.views_filter, data))
        # 首单库存时间 过滤
        if self.listing_filter.get("first_time"):
            data = list(filter(self.first_time_filter, data))
        # listing销量 过滤
        if self.listing_filter.get("sale"):
            data = list(filter(self.listing_sale_filter, data))
        # 平台销量 过滤
        if self.listing_filter.get("sku_sales"):
            data = list(filter(self.sku_sales_filter, data))
        # # 上架次数 过滤
        # if self.listing_filter.get("publish_count") or self.listing_filter.get("publish_count")==0:
        #     data = list(filter(self.publish_count_filter, data))
        print(data)
        print(len(data))

    def publish_count_filter(self, sku):
        sql = """
        SELECT count(1) as num FROM test_zhcxkj_center_product.t_shopee_listing_sku WHERE source_id=%s AND sku=%s;
        """
        res = self.db.fetch_all(sql, (self.source_id, sku))
        if res:
            count = res[0].get('num')
        else:
            count = 0
        if count <= self.listing_filter.get("publish_count"):
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 上架次数过滤了sku={sku}")
            return False

    def filter_listing(self, sku):
        sql2 = """
        SELECT b.id FROM test_zhcxkj_center_product.t_shopee_listing_sku a 
        LEFT JOIN test_zhcxkj_center_product.t_shopee_listing b ON a.listing_id=b.id
        WHERE a.source_id=%s
        AND a.sku=%s
        AND b.`status` =1;
        """
        res2 = self.db.fetch_all_tuple(sql2, (self.source_id, sku))
        if res2:
            INFO.logger.info(f"source_id= {self.source_id} t_shopee_listing_sku表过滤了sku={sku}")
            return False
        else:
            return True

    def filter_queue_detail(self, sku):
        sql3 = """
        SELECT id FROM test_zhcxkj_center_strategy.t_shopee_publish_queue_detail 
        WHERE source_id=%s AND sku=%s AND `Status` IN (0,1,2);
        """
        res2 = self.db.fetch_all_tuple(sql3, (self.source_id, sku))
        if res2:
            INFO.logger.info(f"source_id= {self.source_id} t_shopee_publish_queue_detail表过滤了sku={sku}")
            return False

        else:
            return True

    def dynamic_inventory_filter(self, sku):
        good_num = self.get_good_num(sku)
        if self.listing_filter.get("good_num")[0] <= good_num <= self.listing_filter.get("good_num")[1]:
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 动态库存过滤了sku={sku}")
            return False

    def views_filter(self, sku):
        # 这里取得的平台的sku的总浏览量  不在取店铺的
        sql = """
        SELECT SUM(b.views) as views
        FROM test_zhcxkj_center_product.t_shopee_listing_sku a
        LEFT JOIN test_zhcxkj_center_product.t_shopee_listing_extra b ON a.listing_id=b.listing_id
        WHERE  a.sku=%s
        """
        res = self.db.fetch_all(sql, (sku,))

        if res:
            if res[0].get('views'):
                views = res[0].get('views')
            else:
                views = 0
            if self.listing_filter.get('views')[0] <= views <= self.listing_filter.get('views')[1]:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} 浏览量过滤了sku={sku}")
                return False
        else:
            INFO.logger.info(f"source_id= {self.source_id} 浏览过滤了sku={sku}")
            return False

    def filter_listing_by_first_publish_time(self, listing_id):
        first_publish_time = listing_id[1]
        first_publist_time_min = datetime.datetime.strptime(self.listing_filter.get("first_publist_time")[0],
                                                            '%Y-%m-%d %H:%M:%S')
        first_publist_time_max = datetime.datetime.strptime(self.listing_filter.get("first_publist_time")[1],
                                                            '%Y-%m-%d %H:%M:%S')
        if first_publish_time:
            if first_publist_time_min <= first_publish_time <= first_publist_time_max:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了，原因是不符合上架到店铺时间")
                return False
        else:
            INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了，没有查询到上架到店铺时间")
            return False

    def listing_sale_filter(self, listing_id):
        # 这里取得的Shopee平台listing销量 （t_shopee_listing_extra）
        get_listing_sale = """
        SELECT sale
        FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_extra a
        WHERE  a.listing_id=%s
        """
        res = self.db.fetch_all(get_listing_sale, (listing_id[0],))

        if res:
            sale = res[0].get('sale')
            if self.listing_filter.get('sale')[0] <= sale <= self.listing_filter.get('sale')[1]:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]} 原因是listing销量")
                return False
        else:
            INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]} 原因是listing销量")
            return False

    def sku_sales_filter(self, sku):
        #   根据策略规则获取销量

        sales = self.get_platform_sales(sku, self.listing_filter.get("sku_sales_platform"),
                                        self.listing_filter.get("sku_sales_days"))
        # 判断销量是否符合规则
        if self.listing_filter.get("sku_sales")[0] <= sales <= self.listing_filter.get("sku_sales")[1]:
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 平台总销量过滤了sku={sku}")
            return False

    def first_time_filter(self, sku):
        sql1 = """
        SELECT check_time FROM test_zhcxkj_center_product.rb_product_admin WHERE sku=%s
        """
        res1 = self.db.fetch_all(sql1, (sku,))
        if res1[0].get('check_time'):
            dt_str = res1[0].get('check_time').strftime('%Y-%m-%dT%H:%M:%S')
        else:
            ERROR.logger.error(f"{sku}终审时间为空")
            return False
        headers = {
            "Authorization": "Basic YXBpdXNlcjpFN0kxLWxJX2QjOGRscw=="
        }
        url = "http://erpapi.zhcxkj.com/Product/MarketPublish/GetSKUWarehousing"
        json_data = {
            "current": 1,
            "pageSize": 500,
            "input": {
                "CustomerId": 1,
                "CheckTime": dt_str,
                "Skus": [sku]
            }
        }
        res2 = requests.post(url=url, json=json_data, headers=headers).json()
        if res2.get("data"):
            puttime = res2.get('data')[0].get('putTime')
            puttime = datetime.datetime.strptime(puttime, '%Y-%m-%d %H:%M:%S.%f')
            first_time_min = datetime.datetime.strptime(self.listing_filter.get("first_time")[0], '%Y-%m-%d %H:%M:%S')
            first_time_max = datetime.datetime.strptime(self.listing_filter.get("first_time")[1], '%Y-%m-%d %H:%M:%S')
            if first_time_min <= puttime <= first_time_max:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} 首单库存过滤了sku={sku}")
                return False
        else:
            INFO.logger.info(f"source_id= {self.source_id} sku={sku}首单库存没有查询到")
            INFO.logger.info(f"source_id= {self.source_id} 首单库存过滤了sku={sku}")
            return False

    def get_good_num(self, sku):
        is_group_sql = """SELECT is_group FROM test_zhcxkj_center_product.rb_product WHERE sku=%s;"""
        is_group_sql_res = self.db.fetch_all(is_group_sql, (sku,))
        if is_group_sql_res:
            if is_group_sql_res[0]['is_group'] == 0:
                filter_good_num = """
                SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory WHERE sku=%s;
                """
                res1 = self.db.fetch_all(filter_good_num, (sku,))
                if res1[0].get('good_num'):
                    # print(f"{sku}动态库存：{res1[0].get('good_num')}")
                    return res1[0].get('good_num')
                else:
                    # print(f"{sku}动态库存：0")
                    return 0
            else:
                get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                                "WHERE group_sku=%s;"
                get_child_sku_res = self.db.fetch_all(get_child_sku, (sku,))

                if get_child_sku_res:
                    sku_list = []
                    for child in get_child_sku_res:
                        filter_good_num = """
                            SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory 
                            WHERE sku = %s  group by sku;"""
                        filter_good_num_res = self.db.fetch_all(filter_good_num, (child.get('sku'),))
                        if filter_good_num_res:
                            good_num = filter_good_num_res[0].get('good_num')
                        else:
                            good_num = 0
                        dongtaikucun = int(good_num / (child.get('sku_num')))
                        sku_list.append((child.get('sku'), dongtaikucun))
                    min_sku = sorted(sku_list, key=lambda x: x[1], reverse=False)[0]
                    finally_good_num = min_sku[1]
                    # print(f"{sku}动态库存：{finally_good_num}")
                    return finally_good_num
                else:
                    # print(f"{sku}在rb_product_group_sku表没查询到子sku")
                    return 0

    def get_platform_sales(self, sku, orderSourceType, sku_sales_days):
        """
        得到各个平台销量
        """
        url = """https://api-dw.zhcxkj.com/dw/erp/salesvolume/platform/salessku"""
        if orderSourceType != "all":
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": orderSourceType,
                    "skus": [sku],
                    "days": sku_sales_days
                }
            }
        else:
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": None,
                    "skus": [sku],
                    "days": sku_sales_days
                }
            }
        hearders = {
            "Content-Type": "application/json",
            "Authorization": "Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA==",
        }

        res = requests.post(url=url, headers=hearders, json=param)

        sales = 0
        if isinstance(orderSourceType, int):
            for data in res.json().get('data'):
                if data.get('orderSourceType') == orderSourceType:
                    sales += data.get('salesVolume')
            return sales
        else:
            for data in res.json().get('data'):
                sales += data.get('salesVolume')
            return sales

    def get_profit_margin(self,ordersource_id,country,sku,calculationformulaid,price):
        url = "http://priceapi.zhcxkj.com/api/calculationFormula/calculationPrice/getCalculationGrossProfitMarginViewModelList"
        headers = {
            "Authorization":"Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA==",

        }
        req_json = {
            "language": "zh_CN",
            "input": [
                {
                    "customerId": 1,
                    "BusinessTypeCode": "ShopeeV2LocalWarehouse",
                    "PlatformId": 97,
                    "WarehouseTypeId": 1,
                    "WarehouseId": 5312,
                    "orderSourceId": ordersource_id,
                    "Country": country,
                    "Sku": sku,
                    "CalculationFormulaId": calculationformulaid,
                    "Price": price
                }
            ]
        }
        res = requests.post(url=url,headers=headers,json=req_json)
        if res.status_code == 200:
            return res.json().get('data')[0].get('GrossProfitMargin')
        else:
            return False


if __name__ == '__main__':
    # SkuFilter().sku_filter(14)
    print(SkuFilter().get_good_num("10059178"))
    # SkuFilter().get_platform_sales("16089737",67,None)
    # print(SkuFilter().get_platform_sales("16926140", 70, None))
    # orderSourceType_list = [2, 57]
    # sku = "10003056"
    # print(SkuFilter().first_time_filter("10001240"))
